﻿namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    partial class DAL_M014_MerberBuy
    {
        #region Insert_SQL
        private static string _insert_SQL = @"Insert Into M014_MerberBuy
(CREATE_DATETIME,
UPDATE_DATETIME,
TRANS_STATE,
UPDATER_ID,
TERMINAL_CD,
ID,
USER_ID,
GOODS_NAME,
PRICE,
NUITS,
TOTAL,
DATETEME)
values 
(CONVERT(VARCHAR,GETDATE(),112) + REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',''),
CONVERT(VARCHAR,GETDATE(),112) + REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',''),
'1',
@UPDATER_ID,
@TERMINAL_CD,
@ID,
@USER_ID,
@GOODS_NAME,
@PRICE,
@NUITS,
@TOTAL,
@DATETEME)";
        #endregion

        #region Update_SQL

        private static string _update_SQL = @"Update M014_MerberBuy set 
UPDATE_DATETIME=CONVERT(VARCHAR,GETDATE(),112) + REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',''),
TRANS_STATE='2',
UPDATER_ID=@UPDATER_ID,
TERMINAL_CD=@TERMINAL_CD,
USER_ID=@USER_ID,
GOODS_NAME=@GOODS_NAME,
PRICE=@PRICE,
NUITS=@NUITS,
TOTAL=@TOTAL,
DATETEME=@DATETEME
Where ID=@ID";

        #endregion

        #region Delete_SQL

        private static string _delete_SQL = @"Delete From M014_MerberBuy Where ID=@ID";

        #endregion

        #region GetList_SQL

        private static string _getList_SQL = @"SELECT * FROM (
SELECT  SUBSTRING(M010.BUSINESS_TIME,1,4)+'-'+SUBSTRING(M010.BUSINESS_TIME,5,2)+'-'+SUBSTRING(M010.BUSINESS_TIME,7,2) AS BUSINESS_TIME,
        M007.GOODS_NM1 ,
        '' AS ID ,
        (case when M009.SELLING_PRICE < 0 then 0 else M009.SELLING_PRICE end) as SELLING_PRICE,
        -M009.UNITS AS UNITS ,
        ((case when M009.SELLING_PRICE < 0 then 0 else M009.SELLING_PRICE end) * -M009.UNITS) AS AllPrice ,
        M012.USER_NAME ,
        M012.USER_NO
FROM    dbo.M009_STORAGE M009
        INNER JOIN dbo.M010_BILL M010 ON M010.BILL_ID = M009.BILL_ID
        LEFT JOIN dbo.M007_GOODS M007 ON M007.GOODS_ID = M009.GOODS_ID
        LEFT JOIN dbo.M012_MEMBER M012 ON M012.USER_NO = M010.USER_NO
WHERE   M010.USER_NO = @USER_ID

UNION

SELECT  CONVERT(VARCHAR, M014.DATETEME, 23) AS BUSINESS_TIME ,
        M014.GOODS_NAME AS GOODS_NM1 ,
        M014.ID ,
        ( CASE WHEN M014.PRICE < 0 THEN ''
               ELSE CONVERT(VARCHAR, M014.PRICE)
          END ) AS SELLING_PRICE ,
        ( CASE WHEN M014.NUITS < 0 THEN ''
               ELSE CONVERT(VARCHAR, M014.NUITS)
          END ) AS NUITS ,
        M014.TOTAL AS AllPrice ,
        M012.USER_NAME ,
        M014.USER_ID AS USER_NO
FROM    M014_MerberBuy M014
        LEFT JOIN M012_MEMBER M012 ON M012.USER_NO = M014.USER_ID
WHERE   M014.USER_ID = @USER_ID
) T ORDER BY T.BUSINESS_TIME DESC ";

        #endregion

        public static string Insert_SQL
        {
            get{return _insert_SQL;}
        }

        public static string Update_SQL
        {
            get { return _update_SQL; }
        }

        public static string Delete_SQL
        {
            get { return _delete_SQL; }
        }

        public static string GetList_SQL
        {
            get { return _getList_SQL; }
        }
    }
}
